package com.lee.mysql.service;

import com.lee.mysql.pojo.TableDbInfo;
import com.lee.mysql.util.JdbcUtil;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Scope;
import org.springframework.context.annotation.ScopedProxyMode;
import org.springframework.stereotype.Service;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.StringJoiner;

/**
 * 表数据同步
 */
@Service
@Scope(proxyMode = ScopedProxyMode.TARGET_CLASS)
public class TableDataSyncService {
    private static final Logger log = LoggerFactory.getLogger(TableDataSyncService.class);

    @Autowired
    private TableDbInfoService tableDbInfoService;



    /**
     * [读取数据库配置方式]
     *
     * 从源数据库同步表(单个/多个)数据到其他数据库
     */
    public int syncTableData(String configId, List<String> tableList,Integer size){
        if (tableList == null || tableList.size() < 1) return 0;
        int i = 0;

        // 获取服务器信息
        List<TableDbInfo> dbList = tableDbInfoService.queryByConfigId(configId);
        if (dbList == null || dbList.size() < 2) return 0;

        TableDbInfo md = dbList.get(0);
        TableDbInfo td = dbList.get(1);

        Connection masterConn = null;
        Connection targetConn = null;

        Statement masterStat = null;
        Statement targetStat = null;

        ResultSet mrs = null;
        try {
            masterConn = JdbcUtil.getConn2(md.getDbHost(), md.getDataBase(), md.getDbUsername(), md.getDbPassword(), md.getCharSet());
            masterStat = masterConn.createStatement();
            targetConn = JdbcUtil.getConn2(td.getDbHost(), td.getDataBase(), td.getDbUsername(), td.getDbPassword(), td.getCharSet());
            targetConn.setAutoCommit(false);
            targetStat = targetConn.createStatement();

            //获取每次读取数据条数
            if (size == null || size < 1){
                size = 1000;
            }

            for (String tab : tableList) {
                //获取表数据总数
                int total = 0;
                String totalSql = "SELECT count(*) total FROM "+tab;
                mrs = masterStat.executeQuery(totalSql);
                if (mrs.next()){
                    total = mrs.getInt("total");
                }

                if (total < 1) continue;

                //获取表字段类型
                List<String> colTypeList = new ArrayList<>();
                String typeSql = "SELECT DATA_TYPE dataType FROM information_schema.COLUMNS" +
                        " WHERE TABLE_SCHEMA = '" + md.getDataBase() +
                        "' AND TABLE_NAME = '"+ tab + "' ORDER BY ORDINAL_POSITION ASC";
                mrs = masterStat.executeQuery(typeSql);
                while (mrs.next()){
                    colTypeList.add(mrs.getString("dataType").toUpperCase());
                }

                //获取表字段
                List<String> colList = new ArrayList<>();
                StringJoiner columns1 = new StringJoiner(",");
                String colSql = "SELECT COLUMN_NAME colName FROM information_schema.COLUMNS" +
                                " WHERE TABLE_SCHEMA = '" + md.getDataBase() +
                                "' AND TABLE_NAME = '"+ tab + "' ORDER BY ORDINAL_POSITION ASC";
                mrs = masterStat.executeQuery(colSql);
                while (mrs.next()){
                    String colName = mrs.getString("colName");
                    colList.add(colName);
                    columns1.add(colName);
                }
                String columns2 = "("+columns1+")";

                //获取循环次数
                int num = (total / size) + 1;

                // 清空目标表
                String delSql = "TRUNCATE TABLE " + tab;
                targetStat.executeUpdate(delSql);

                for (int j = 0; j < num; j++) {
                    //读取源库数据
                    List<String> dataList = new ArrayList<>();
                    String readSql = "SELECT " + columns1 + " FROM " + tab + " LIMIT " + (j*size) + "," + size;

                    mrs = masterStat.executeQuery(readSql);
                    while (mrs.next()){
                        StringJoiner data1 = new StringJoiner(",","(",")");
                        for (int k = 0; k < colList.size(); k++) {
                            String c = colList.get(k);
                            String d2 = mrs.getString(c);
                            String type = colTypeList.get(k);
                            if (d2 == null || "NULL".equals(d2.toUpperCase())){
                                data1.add("NULL");
                            }else {
                                // 检测字段类型是否是字符串
                                boolean isStr = checkIsStr(type);
                                if (isStr){
                                    data1.add("'"+ d2 +"'");
                                }else {
                                    data1.add(d2);
                                }
                            }

                        }
                        dataList.add(data1.toString());
                    }

                    StringJoiner data = new StringJoiner(",");
                    for (String s : dataList) {
                        data.add(s);
                    }

                    //写入目标库
                    String writeSql = "INSERT INTO "+ tab + columns2 +" VALUES " + data;
                    log.info("同步写入语句: "+writeSql);
                    targetStat.execute(writeSql);
                }

                targetConn.commit();
                i++;
            }

        }catch (Exception e){
            log.error("数据同步出错",e);
        }finally {
            // 释放资源
            JdbcUtil.close(mrs,masterStat,masterConn);
            JdbcUtil.close(targetStat,targetConn);
        }

        return i;
    }


    private boolean checkIsStr(String type){
        boolean isStr = true;

        if ("INT".equals(type) || "TINYINT".equals(type) || "BIGINT".equals(type)
                || "FLOAT".equals(type) || "DOUBLE".equals(type) || "DECIMAL".equals(type)){
            isStr = false;
        }

        return isStr;
    }

}
